﻿
/****** Object:  UserDefinedFunction [dbo].[Fun_SeasonMatchList]    Script Date: 10/28/2011 14:15:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Fun_SeasonMatchList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[Fun_SeasonMatchList]
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[Fun_SeasonMatchList]
(	
	-- Add the parameters for the function here
	@leagueID uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT match.MatchDateTime, league.LeagueName, match.RoundNumber, homeTeam.TeamName AS HomeTeam
		, visitingTeam.TeamName AS VisitingTeam, homeTeamData.Goal AS HomeGoal
		, visitingTeamData.Goal AS VisitingGoal
	FROM LeagueMatch match
	LEFT JOIN LeagueInfo league ON match.LeagueID = league.LeagueID
	LEFT JOIN LeagueTeam homeTeam ON match.HomeTeamID = homeTeam.TeamID
	LEFT JOIN LeagueTeam visitingTeam ON match.VisitingTeamID = visitingTeam.TeamID
	LEFT JOIN LeagueTeamData homeTeamData 
		ON match.MatchID = homeTeamData.MatchID AND homeTeam.TeamID = homeTeamData.TeamID
	LEFT JOIN LeagueTeamData visitingTeamData 
		ON match.MatchID = visitingTeamData.MatchID AND visitingTeam.TeamID = visitingTeamData.TeamID
	WHERE league.LeagueID=@leagueID
)

GO


